package com.wei.dao;

import com.wei.pojo.Sp;
import com.wei.tools.DBconn;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 魏小杭
 */
public class SpDao {

    //定义数据库连接对象
    private static Connection conn=null;
    //定义数据库操作对象
    private static PreparedStatement pstm=null;
    //定义数据库结果集对象
    private static ResultSet rs=null;

    /**
     * 分页
     * @author 小杭呀
     * @param page
     * @param count
     * @return
     *
     */
    public List<Sp> selectSpByPage(int page, int count) {
        List<Sp> spList = new ArrayList<>();
        try {
            conn = DBconn.getConn();
            String sql = "select * from sp limit ?,?";
            pstm = conn.prepareStatement(sql);
            //计算页码
            int pages = (page-1)*count;
            pstm.setInt(1, pages);
            pstm.setInt(2, count);
            rs = pstm.executeQuery();
            while (rs.next()){
                Sp sp = new Sp();
                sp.setId(rs.getInt("id"));
                sp.setName(rs.getString("name"));
                sp.setPrice(rs.getString("price"));
                sp.setNumber(rs.getInt("number"));
                //将数据插进去
                spList.add(sp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return spList;
    }

    /**
     * 查询总页数
     *
     * @param count
     * @return
     * 公式：总条数/每夜条数 + (如果有余数+1 没有+0)
     *  总页数 =    总条数 / 每页条数 + （总条数 % 每页条数 > 0 ? 1:0）
     */
    public int selectSumPage(int count) {
        int sumpage = 0;
        int sumCount = 0;
        try {
            conn = DBconn.getConn();
            // 查询总条数
            String sql = "select count(*) from sp";
            pstm = conn.prepareStatement(sql);

            rs =  pstm.executeQuery();
            if(rs.next()) {
                sumCount = rs.getInt(1);
            }
            //带入公式
            sumpage = sumCount / count +(sumCount % count > 0?1:0);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sumpage;
    }

//    public static void main(String[] args) {
//        int list = new SpDao().selectSumPage(5);
//        System.out.println(list);
//    }
}
